
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EBAS_GRUPPE_FAG_EBAS_FAG]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EBAS_GRUPPE_FAG] DROP CONSTRAINT FK_EBAS_GRUPPE_FAG_EBAS_FAG
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EBAS_FAG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EBAS_FAG]
GO

CREATE TABLE [dbo].[EBAS_FAG] (
	[GUID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
	[NAVN] [varchar] (255) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
	[BOGPAKKE_GUID] [uniqueidentifier] NULL ,
	[KARAKTER_SKALA_GUID] [uniqueidentifier] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EBAS_FAG] WITH NOCHECK ADD 
	CONSTRAINT [PK_EBAS_FAG] PRIMARY KEY  CLUSTERED 
	(
		[GUID]
	)  ON [PRIMARY] 
GO

 CREATE  UNIQUE  INDEX [IDX_EBAS_FAG_NAVN] ON [dbo].[EBAS_FAG]([NAVN]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EBAS_FAG] ADD 
	CONSTRAINT [FK_EBAS_FAG_EBAS_BOGPAKKE] FOREIGN KEY 
	(
		[BOGPAKKE_GUID]
	) REFERENCES [dbo].[EBAS_BOGPAKKE] (
		[GUID]
	),
	CONSTRAINT [FK_EBAS_FAG_EBAS_KARAKTER_SKALA] FOREIGN KEY 
	(
		[KARAKTER_SKALA_GUID]
	) REFERENCES [dbo].[EBAS_KARAKTER_SKALA] (
		[GUID]
	)
GO

-- Bogpakke 1

INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Managing People', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 1'
	
INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Managing Marketing', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 1'

INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Managing Performance', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 1'

INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Business Transformations', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 1'

INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Elective Variants', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 1'
	
INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Dissertation', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 1'
	
INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Exam Part I', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 1'
	
INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Exam Part III', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 1'
	
-- Bogpakke 2

INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Managing Information', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 2'
	
INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Managing Financial Recources', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 2'
	
INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Strategic Direction', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 2'
	
INSERT INTO EBAS_FAG
	SELECT NEWID(), 'Exam Part II', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 2'
	
INSERT INTO EBAS_FAG
	SELECT NEWID(), 'MBA Executive', BP.GUID, S.GUID
	FROM EBAS_BOGPAKKE BP, EBAS_KARAKTER_SKALA S
	WHERE S.NAVN = 'US-skala'
	AND BP.NAVN = 'Bogpakke 2'	
